# -*_ codeing=utf-8 -*-
# @Time: 2024/12/10 11:13
# @Author: foxhuty
# @File: processing_by_good_scores.py
# @Software: PyCharm
# @Based on python 3.10
import pandas as pd
from sympy import symbols, solve
import numpy as np
import os
import time


class GaokaoData2025:
    """
    从2025年起四川采用新高考（3+1+2)模式。这是专门针对新高考模式的政治, 地理, 生物, 化学四门赋分学科而设计的程序，
    以帮助高中年级老师做成绩分析用。还可以计算各班平均分及占比，有效分统计等。
    该程序设计用于帮助高中教师分析新高考模式下的学生成绩，特别是政治、地理、生物和化学四门学科的赋分情况。
    程序可以读取Excel文件中的成绩数据，计算各学科的赋分等级和赋分值，统计班级平均分，各科有效分并生成包含这些分析结果的Excel文件。
    注意：python 3.13,pandas 2.4 版本测试正常。在 python 3.9,pandas 1.4版本测试中，把 map换成applymap后,程序可正常运行。
    """
    # 各个等级赋分区间（T1——T2）
    # 正常赋分区间(满分赋值)
    A_T_range = [86, 100]
    B_T_range = [71, 85]
    C_T_range = [56, 70]
    D_T_range = [41, 55]
    E_T_range = [30, 40]
    # 降分赋值区间，适用于学校考试，样本不足
    lower_A_T_range = [76, 90]
    lower_B_T_range = [61, 75]
    lower_C_T_range = [46, 60]
    lower_D_T_range = [31, 45]
    lower_E_T_range = [20, 30]

    # line_ratio = None
    physics_line_ratio = None
    history_line_ratio = None
    flag = True
    root_dir_path = 'D:/成绩统计结果'

    def __init__(self, file):
        self.file = file
        try:
            excel_file = pd.ExcelFile(self.file)
        except FileNotFoundError as e:
            raise IOError(f'无法读取文件：{e}')
        self.sheet_names = [sheet_name for sheet_name in excel_file.sheet_names if
                            sheet_name in ['物理类', '历史类', '总表']]
        # 清洗原数据中的列名空格和数据空格，获取清洗后的df列表
        self.data_list = [
            self.clean_spaces(pd.read_excel(self.file, sheet_name=sheet_name, dtype={'考号': str, '考生号': str})) for
            sheet_name in
            self.sheet_names]

    def __str__(self):
        return os.path.basename(self.file)

    def get_grade_data(self):
        """
        分别获取政治，地理，化学和生物学科的赋分等级和赋分值后生成excel数据表
        :return: None
        """
        data = self.data_list
        data_added = []
        for item in range(len(data)):
            subjects = [col for col in data[item].columns if col in ['政治', '地理', '生物', '化学']]
            # print(f'{self.sheet_names[item]}各学科等级人数及卷面分值区间'.center(100, '*'))
            subject_added_data = []
            for subject in subjects:
                data_item = self.get_grade(data[item], subject)
                subject_added_data.append(data_item)
            subjects_added_data = subject_added_data[-1]
            # subjects_added_data = pd.concat(subject_added_data, ignore_index=False,join='inner', axis=1)
            subjects_added_data_cols = [col for col in subjects_added_data.columns if
                                        col in ['语文', '数学', '英语', '物理', '历史', '政治赋分', '地理赋分',
                                                '生物赋分', '化学赋分']]
            # 新版pandass要用astype(float).round(2)转换成2位小数。
            subjects_added_data['总分赋分'] = subjects_added_data[subjects_added_data_cols].sum(axis=1).astype(
                float).round(2)
            subjects_added_data.sort_values(by='总分赋分', ascending=False, inplace=True)
            subjects_added_data['序号'] = [i + 1 for i in range(len(subjects_added_data))]
            data_added.append(subjects_added_data)
        return data_added

    def get_grade(self, data, subject):
        """
        分别获取政治，地理，化学和生物学科的赋分等级和赋分值
        :param data: df数据表
        :param subject: 学科
        :return: 返回获取了赋分等级和赋分值的df数据
        """
        max_score, min_score = self.get_subject_max_min_score(data, subject)
        data[subject + '等级'] = data[subject].apply(lambda x: self.get_level(x, min_score))
        data[subject + '赋分'] = data[subject].apply(
            lambda x: self.get_final_scores(x, min_score, max_score))
        return data

    @staticmethod
    def separate_data(data_mixed):
        """
         用于分科后考试，赋分后再生成物理类和历史类两个df，用于生成excel文件
        :param data_mixed:
        :return:物理类和历史类两个df
        """

        history_min = data_mixed['历史'].min(skipna=True)
        physics_min = data_mixed['物理'].min(skipna=True)
        data_history = data_mixed[data_mixed['历史'] >= history_min]
        # 删除多列
        data_history = data_history.copy()
        data_history.drop(['物理', '化学', '化学等级', '化学赋分'], axis=1, inplace=True)

        data_physics = data_mixed[data_mixed['物理'] >= physics_min]
        # 删除单列
        data_physics = data_physics.copy()
        del data_physics['历史']
        return data_physics, data_history

    def subjects_average(self, data, subjects):
        """
         计算各学科的平均分。
        :param data: df数据
        :param subjects: 参加计算平均分的学科
        :return: 返回一个df。包含有各班各科平均分，年级平均分和各科平均分在年级平均分中的占比，用于制作折线图
        """
        class_av = data.groupby('班级')[subjects].mean().round(2)
        # class_av.fillna(0, inplace=True)
        # 求这个平均数，只能用apply(np.mean)才行。用其它的如mean(),apply(np.nanmean)都会报错
        av_general = data[subjects].apply(np.mean, axis=0).round(2)
        # av_general = data[subjects].mean().round(2)
        # av_general.fillna(0, inplace=True)
        av_percentage = class_av / av_general.round(2)
        # av_percentage.fillna(0, inplace=True)
        # print(av_percentage)

        # pandas 2.0以上用map替换applymap
        av_percentage = av_percentage.map(lambda x: format(x, '.2%'))  # 以百分号显示
        av_percentage = av_percentage.map(lambda x: x.replace('nan%', ''))  # 不显示nan%

        av_percentage_cols = [col + '占比' for col in av_percentage.columns]
        col_dict = dict(zip(av_percentage.columns, av_percentage_cols))
        av_percentage.rename(columns=col_dict, inplace=True)

        class_av.loc['年级平均'] = av_general
        class_av['参考人数'] = data['班级'].value_counts()
        class_av.loc['年级平均', '参考人数'] = class_av['参考人数'].sum()
        final_av_percentage = pd.concat([class_av, av_percentage], axis=1)
        final_av_percentage = self.change_columns_order(final_av_percentage)
        return final_av_percentage

    def get_average_school(self, data):
        """
         计算赋分后作有学科的平均分及平均分占比
        :param data: df
        :return: 返回一个有df元素的列表。
        """
        final_av = []
        subjects_av = [col for col in self.get_subjects(data) if col not in ['化学', '政治', '地理', '生物', '总分']]
        # print(subjects_av)

        final_av_percentage = self.subjects_average(data, subjects_av)
        final_av.append(final_av_percentage)
        return final_av

    @staticmethod
    def get_subjects(data):
        subjects = [col for col in data.columns if
                    col in ['语文', '数学', '英语', '物理', '历史',
                            '化学', '政治', '地理', '生物',
                            '化学赋分', '政治赋分', '地理赋分', '生物赋分', '总分', '总分赋分']]
        return subjects

    def excel_school_files(self):
        """
         学校组织的考试，要对化学政治地理和生物四科赋分。excel文件上要有一个总表工作表（sheet)
        """
        # line_percent = format(GaokaoData2025.line_ratio, '.0%')
        # physics_line_percentage = format(GaokaoData2025.physics_line_ratio, '.0%')
        # history_line_percentage = format(GaokaoData2025.history_line_ratio, '.0%')
        scores_added_data_list = self.get_grade_data()
        # file_name = os.path.basename(
        #     self.file).split('.')[0] + f'--物理类{physics_line_percentage}+历史类{history_line_percentage}划线成绩分析统计结果.xlsx'
        # file_name = self.filename_by_time()
        excel_file_name, output_dir = self.get_excel_name()
        # excel_file_name, output_dir = self.get_excel_name_by_category(history_ratio=GaokaoData2025.history_line_ratio,
        #                                                               physics_ratio=GaokaoData2025.physics_line_ratio)

        # with pd.ExcelWriter('D:\\成绩统计结果\\'+file_name) as writer:
        writer = pd.ExcelWriter(output_dir)
        for item in range(len(scores_added_data_list)):
            scores_added_data_list[item].to_excel(writer, sheet_name=f'{self.sheet_names[item]}-赋分表',
                                                  index=False,
                                                  float_format='%.2f')
            physics_data, history_data = self.separate_data(scores_added_data_list[item])
            physics_data.reset_index(drop=True, inplace=True)
            history_data.reset_index(drop=True, inplace=True)
            physics_data['排名'] = physics_data['总分赋分'].rank(method='min', ascending=False)
            physics_data['序号'] = [i + 1 for i in physics_data.index]
            history_data['排名'] = history_data['总分赋分'].rank(method='min', ascending=False)
            history_data['序号'] = [i + 1 for i in history_data.index]

            average_data_physics = self.get_average_school(physics_data)
            average_data_history = self.get_average_school(history_data)
            average_data_physics[item].to_excel(writer, sheet_name='物理类平均分', float_format='%.2f')
            average_data_history[item].to_excel(writer, sheet_name='历史类平均分', float_format='%.2f')

            good_scores_data_physics, unmatched_physics, assessment_physics = self.good_scores_school(
                physics_data, GaokaoData2025.physics_line_ratio)
            good_scores_data_history, unmatched_history, assessment_history = self.good_scores_school(
                history_data, GaokaoData2025.history_line_ratio)
            good_scores_data_history.to_excel(writer, sheet_name='历史类有效分')
            good_scores_data_physics.to_excel(writer, sheet_name='物理类有效分')
            physics_order_by_class = self.split_into_class(physics_data)
            history_order_by_class = self.split_into_class(history_data)
            physics_order_by_class.to_excel(writer, sheet_name='物理类',
                                            index=False)
            history_order_by_class.to_excel(writer, sheet_name='历史类',
                                            index=False)

            unmatched_physics.to_excel(writer, sheet_name='物理类错位生名单', index=False)
            unmatched_history.to_excel(writer, sheet_name='历史类错位生名单', index=False)

            assessment_physics.to_excel(writer, sheet_name='物理类学科评价')
            assessment_history.to_excel(writer, sheet_name='历史类学科评价')
            writer.close()
            return excel_file_name, average_data_history[item], average_data_physics[
                item], good_scores_data_history, good_scores_data_physics

    def get_final_scores(self, score, min_score, max_score):
        """
        计算获取学生卷面分数的赋分值
        :param score:
        :param min_score:
        :param max_score:
        :return:
        """
        a_t, b_t, c_t, d_t, e_t = self.get_t_range()
        if score >= min_score[0]:
            score_added = self.get_added_score(score, min_score[0], max_score[0], a_t[0], a_t[1])
            return score_added
        elif score >= min_score[1]:
            score_added = self.get_added_score(score, min_score[1], max_score[1], b_t[0], b_t[1])
            return score_added
        elif score >= min_score[2]:
            score_added = self.get_added_score(score, min_score[2], max_score[2], c_t[0], c_t[1])
            return score_added
        elif score >= min_score[3]:
            score_added = self.get_added_score(score, min_score[3], max_score[3], d_t[0], d_t[1])
            return score_added
        elif score < min_score[3]:
            score_added = self.get_added_score(score, min_score[4], max_score[4], e_t[0], e_t[1])
            return score_added

    @staticmethod
    def get_t_range():
        """
        当flag为True,默认为降分赋值，适用于年级考试用，样本不足。
        当flag为False,正常满分赋值，适用于样本充足。
        :return: 各等级赋分区间
        """
        if GaokaoData2025.flag:
            a_t = GaokaoData2025.lower_A_T_range
            b_t = GaokaoData2025.lower_B_T_range
            c_t = GaokaoData2025.lower_C_T_range
            d_t = GaokaoData2025.lower_D_T_range
            e_t = GaokaoData2025.lower_E_T_range
            return a_t, b_t, c_t, d_t, e_t
        else:
            a_t = GaokaoData2025.A_T_range
            b_t = GaokaoData2025.B_T_range
            c_t = GaokaoData2025.C_T_range
            d_t = GaokaoData2025.D_T_range
            e_t = GaokaoData2025.E_T_range
            return a_t, b_t, c_t, d_t, e_t

    def good_scores_school(self, data, line_ratio):
        """
        用于学校考试的有效分统计，有效分数是由指定的划线分数计算得到
        :param data: df
        :param line_ratio:
        :return: 返回一个有效分统计的df
        """
        good_scores_data, unmatched_student_names, assessment_data = self.get_single_double_school_data(data,
                                                                                                        line_ratio)
        return good_scores_data, unmatched_student_names, assessment_data

    def get_single_double_school_data(self, data, line_ratio):
        """
         按照新高考的模式，计算中线和高线的有效分，完成单有效和双有效的统计
        :param data:
        :param line_ratio:
        :return: 返回含有有效分数，单有效和双有效的df

        """
        good_total_line = self.get_good_total_line(data, '总分赋分', line_ratio)
        subjects = [col for col in self.get_subjects(data) if col not in ['化学', '生物', '政治', '地理', '总分']]
        double_subjects_num_list = []
        single_data_list = []
        double_data_list = []
        subjects_scores = []
        unmatched_students_list = []
        unmatched_subjects_num_list = []
        for subject in subjects:
            double_subject_num_series = self.get_double_subject_num_series(data, subject, '总分赋分', good_total_line)
            subject_good_score = self.get_subject_good_score(data, subject, line_ratio)
            subjects_scores.append(subject_good_score)

            single_data = self.get_single_subject_data(data, subject, subject_good_score)
            double_data = self.get_double_subject_data(data, subject, '总分赋分', subject_good_score,
                                                       good_total_line)
            subject_unmatched_student = self.get_unmatched_subject_students(data, '总分赋分', subject,
                                                                            subject_good_score, good_total_line)
            unmatched_subject_num = subject_unmatched_student.groupby('班级')[subject].count()
            double_subjects_num_list.append(double_subject_num_series)
            unmatched_subjects_num_list.append(unmatched_subject_num)
            unmatched_students_list.append(subject_unmatched_student)
            single_data_list.append(single_data)
            double_data_list.append(double_data)
        # 计算选科后各班各科总分上线人数
        double_subjects_num_df = pd.concat(double_subjects_num_list, axis=1)
        self.add_total_num_row(double_subjects_num_df)

        # print(double_subjects_num_df)
        # 获取有效分数并转换成df
        good_scores_dict = dict(zip(subjects, subjects_scores))
        good_scores_df = pd.DataFrame(good_scores_dict, index=[0])
        # 合成单有效和双有效统计的df
        single_data = pd.concat(single_data_list, axis=1)
        double_data = pd.concat(double_data_list, axis=1)
        single_data.sort_index(inplace=True)
        double_data.sort_index(inplace=True)
        # 生成各科错位学生名单
        unmatched_students_by_class = pd.concat(unmatched_students_list, axis=1)
        unmatched_students_by_class.dropna(inplace=True, how='all', axis=1)
        # 计算错位人数，用于选科后统计错位生人数
        unmatched_subjects_num = pd.concat(unmatched_subjects_num_list, axis=1)
        self.add_total_num_row(unmatched_subjects_num)
        # unmatched_subjects_num.replace(0, np.nan, inplace=True)
        # 单有效统计：增加一列参考人数和一行年级共计
        single_data['参考人数'] = data['班级'].value_counts()
        self.add_total_num_row(single_data)

        # 增加一列上线率并用百分号显示
        single_data['上线率'] = single_data['总分赋分'] / single_data['参考人数']
        single_data['上线率'] = single_data['上线率'].apply(lambda x: format(x, '.2%'))  # 以百分号显示

        # 双有效统计：增加一列参考人数和一行年级共计
        double_data['参考人数'] = data['班级'].value_counts()
        self.add_total_num_row(double_data)
        # 计算学科评价，获取命中率，贡献率和评价等级
        assessment_df, shoot_df, contribution_df = self.get_assess_grade_df(single_data, double_data,
                                                                            double_subjects_num_df)
        subjects_assessment = [col for col in data.columns if
                               col in ['语文', '数学', '英语', '物理', '历史', '化学赋分', '生物赋分', '政治赋分',
                                       '地理赋分']]
        # 给各班各学科评价赋值：A，B，C，D，E，F（或无数据）
        for subject in subjects_assessment:
            self.grade_assessment(subject, shoot_df, contribution_df, single_data, assessment_df,
                                  double_subjects_num_df)
        # print(assessment_df)
        # 注意：新版用map，旧版用applymap
        shoot_df = shoot_df.map(lambda x: format(x, '.2%'))
        contribution_df = contribution_df.map(lambda x: format(x, '.2%'))
        shoot_contribution_assessment = pd.concat([shoot_df, contribution_df, assessment_df], axis=0,
                                                  keys=['命中率', '贡献率', '学科评价'])

        combined_data = pd.concat([good_scores_df, single_data, double_data, unmatched_subjects_num, assessment_df],
                                  keys=['有效分数', '单有效', '双有效', '错位人数', '学科评价'],
                                  axis=0)
        # 改变列顺序，把参考人数一列放在班级后面
        combined_data = self.change_columns_order(combined_data)

        return combined_data, unmatched_students_by_class, shoot_contribution_assessment

    def get_excel_name(self):
        file_name = self.filename_by_time()
        if not os.path.exists(GaokaoData2025.root_dir_path):
            os.makedirs(GaokaoData2025.root_dir_path)
        output_path = os.path.join(GaokaoData2025.root_dir_path, file_name)
        return file_name, output_path

    def get_excel_name_by_category(self, **kwargs):
        file_name = self.filename_by_category(**kwargs)
        if not os.path.exists(GaokaoData2025.root_dir_path):
            os.makedirs(GaokaoData2025.root_dir_path)
        output_path = os.path.join(GaokaoData2025.root_dir_path, file_name)
        return file_name, output_path

    @staticmethod
    def add_total_num_row(data):
        data.loc['年级共计'] = [data[col].sum() for col in data.columns]

    @staticmethod
    def clean_spaces(data):
        # 清洗列名中的空格
        data.columns = [col.strip() for col in data.columns]
        data.columns = [col.replace(' ', '') for col in data.columns]
        # 清洗数据中的空格
        for col in data.columns:
            if data[col].dtype == 'object':  # 只处理字符串类型的列
                data[col] = data[col].str.strip()
        return data

    @staticmethod
    def get_double_subject_num_series(data, subject, total_col, total):
        """
        新高考中，各班选科人数不同，需要计算出每一个学科的总分上线的人数。
        """
        # 用reindex(class_names, fill_value=0)确保所有班级数据不会消失，没有值时用0代替。
        class_names = data['班级'].drop_duplicates().tolist()
        # 选出各班各个学科的总人数
        subject_data = data[data[subject] >= data[subject].min()]
        # 选出各班各个学科总分上线的人数的series
        double_subject_num_series = subject_data[subject_data[total_col] >= total].groupby('班级')[
            subject].count().reindex(class_names, fill_value=0)

        return double_subject_num_series

    @staticmethod
    def grade_assessment(subject, shoot_df, contribution_df, single_df, grade_df, double_subjects_df):
        """"
        学科等级评价。各班选科各不相同，如某班的一个学科单有效人数为零，则视为这个班没有人选学该学科，等级为无数据或用F表示。
        """

        for i in shoot_df.index:
            if single_df[subject].at[i] != 0:
                if (single_df[subject].at[i]) >= double_subjects_df[subject].at[i] * 0.8:
                    if (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] >= 0.6):
                        grade_df[subject].at[i] = 'A'
                    elif (contribution_df[subject].at[i] >= 0.7) & (shoot_df[subject].at[i] < 0.6):
                        grade_df[subject].at[i] = 'B'
                    elif (contribution_df[subject].at[i] < 0.7) & (shoot_df[subject].at[i] >= 0.6):
                        grade_df[subject].at[i] = 'C'
                    else:
                        grade_df[subject].at[i] = 'D'
                else:
                    grade_df[subject].at[i] = 'E'
            else:
                grade_df[subject].at[i] = '无数据'

    @staticmethod
    def get_assess_grade_df(single, double, subjects_num_df):
        subjects = [subject for subject in single.columns if
                    subject in ['语文', '数学', '英语', '物理', '历史', '化学', '政治', '地理', '生物', '化学赋分',
                                '政治赋分', '地理赋分', '生物赋分']]
        single.fillna(0, inplace=True)
        double.fillna(0, inplace=True)
        subjects_num_df.fillna(0, inplace=True)
        shoot_dict_list = []
        contribution_dict_list = []
        for subject in subjects:
            shoot_subject = double[subject] / single[subject]
            shoot_dict_list.append(shoot_subject)
            # 计算贡献率，要用各班各学科的总分上线人数与该科的双有效人数相除。不能用各班的总分上线人线去除
            # subjects_num_df为所有学科总分上线人数的df(选学科目化学，政治，地理和生物的人数各班是不同的）
            contribution_subject = double[subject] / subjects_num_df[subject]
            contribution_dict_list.append(contribution_subject)
        shoot_dict = dict(zip(subjects, shoot_dict_list))
        shoot_df = pd.DataFrame(shoot_dict)
        contribution_dict = dict(zip(subjects, contribution_dict_list))
        contribution_df = pd.DataFrame(contribution_dict)
        shoot_df.fillna(0, inplace=True)
        contribution_df.fillna(0, inplace=True)
        grade_assess_df = pd.DataFrame(columns=contribution_df.columns, index=contribution_df.index)

        return grade_assess_df, shoot_df, contribution_df

    @staticmethod
    def get_unmatched_subject_students(data, total_col, subject, subject_score, total):
        """
        get the unmatched students in an exam
        :param data:
        :param subject:
        :param subject_score:
        :param total_col:
        :param total:
        :return:
        """
        unmatched_data = data[data[total_col] >= total]
        subject_unmatched_df = unmatched_data.loc[:, ['班级', '姓名', subject]].loc[
            unmatched_data[subject] < subject_score].sort_values(
            by=['班级', subject], ascending=[True, False]).reset_index(drop=True)
        return subject_unmatched_df

    @staticmethod
    def get_subject_max_min_score(data, subject):
        """
        用于计算获取各等级的卷面分区间（Y1-Y2)
        :param data:
        :param subject:
        :return: 各等级的卷面分区间（Y1-Y2)
        """
        max_score = []
        min_score = []
        subject_num = data[subject].count()
        A_num = int(subject_num * 0.15)
        B_num = int(subject_num * 0.35)
        C_num = int(subject_num * 0.35)
        D_num = int(subject_num * 0.13)
        data.sort_values(by=subject, ascending=False, inplace=True, ignore_index=True)
        # 计算A等级的卷面上下限分值及学生人数,由于每个等级的最低分会有不止一个，所以要再次切片
        data_subject_A = data.loc[:A_num - 1, subject]
        A_max = data_subject_A.max()
        A_min = data_subject_A.min()
        max_score.append(A_max)
        min_score.append(A_min)
        # 再次切片，确保A等级所有最低分都在同一个等级
        final_data_subject_A = data[data[subject] >= A_min]
        # 计算B等级的卷面上下限分值及学生人数
        data_subject_B = data.loc[len(final_data_subject_A):(len(final_data_subject_A) + B_num - 1), subject]
        B_max = data_subject_B.max()
        B_min = data_subject_B.min()
        max_score.append(B_max)
        min_score.append(B_min)
        # 再次切片，确保B等级所有最低分都在同一个等级
        final_data_subject_B = data[(data[subject] >= B_min) & (data[subject] <= B_max)]
        # 计算C等级的卷面上下限分值及学生人数
        data_subject_C = data.loc[(len(final_data_subject_A) + len(final_data_subject_B)):(
                len(final_data_subject_A) + len(final_data_subject_B) + C_num - 1), subject]
        C_max = data_subject_C.max()
        C_min = data_subject_C.min()
        max_score.append(C_max)
        min_score.append(C_min)
        # 再次切片，确保C等级所有最低分都在同一个等级
        final_data_subject_C = data[(data[subject] >= C_min) & (data[subject] <= C_max)]
        # 计算D等级的卷面上下限分值及学生人数
        data_subject_D = data.loc[(len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C)):(
                len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C) + D_num - 1), subject]
        d_max = data_subject_D.max()
        d_min = data_subject_D.min()
        max_score.append(d_max)
        min_score.append(d_min)
        # 再次切片，确保D等级所有最低分都在同一个等级
        final_data_subject_D = data[(data[subject] >= d_min) & (data[subject] <= d_max)]
        # 计算E等级的卷面上下限分值及学生人数
        final_data_subject_E = data.loc[
                               (len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C) + len(
                                   final_data_subject_D)):subject_num - 1, subject]
        E_max = final_data_subject_E.max()
        E_min = final_data_subject_E.min()
        max_score.append(E_max)
        min_score.append(E_min)
        # print(
        #     f'{subject}学科A等级人数为：{len(final_data_subject_A)},'
        #     f'B等级人数为：{len(final_data_subject_B)},'
        #     f'C等级人数为：{len(final_data_subject_C)},'
        #     f'D等级人数为：{len(final_data_subject_D)},'
        #     f'E等级人数为：{len(final_data_subject_E)}')
        # 新版pandas2.0以上，接收到的数字在列表中会显示成np.float64(81.0)，所以要用下标读取出来才能正常显示小数
        # print(
        #     f'{subject}学科等级卷面Y1的值分别为：{min_score[0]}, {min_score[1]}, {min_score[2]}, {min_score[3]}, {min_score[4]}, '
        #     f'Y2的值分别为：{max_score[0]}, {max_score[1]}, {max_score[2]}, {max_score[3]}, {max_score[4]}')
        return max_score, min_score

    @staticmethod
    def get_single_subject_data(data, subject, subject_score):
        """
         计算获取一个学科单有效的df
        :param data: df
        :param subject: 学科
        :param subject_score: 一个学科的有效分数
        :return: 返回一个单有效的series
        """
        class_names = data['班级'].drop_duplicates().tolist()
        single_subject = data[data[subject] >= subject_score].groupby(['班级'])[
            subject].count().reindex(class_names, fill_value=0)
        return single_subject

    @staticmethod
    def get_double_subject_data(data, subject, total_col, subject_score, total):
        """
         计算获取学科双有校的df
        :param data: df
        :param subject: 学科
        :param total_col: 总分字段名
        :param subject_score: 学科有效分数
        :param total: 总分
        :return: 返回一个学科双有效的series
        """
        class_names = data['班级'].drop_duplicates().tolist()
        double_subject_data = data[data[total_col] >= total]
        double_subject = double_subject_data[double_subject_data[subject] >= subject_score].groupby('班级')[
            subject].count().reindex(class_names, fill_value=0)
        return double_subject

    @staticmethod
    def get_added_score(y, y1, y2, t1, t2):
        """
        计算赋分值的公式：高考赋分方法，其中y为原始卷面得分，t为赋值得分，
        t1和t2为所在等级赋值区间的下限和上限，y1和y2为卷面所在等级分数区间的下限和上限。
        :param y:
        :param y1:
        :param y2:
        :param t1:
        :param t2:
        :return: 一个学生的赋值得分（四舍五入取整）
        """
        t = symbols('t')
        try:
            if y == y1:
                scores_added = t1
                return scores_added
            else:
                scores_added = solve((t2 - t) / (t - t1) - (y2 - y) / (y - y1), t)
                return round(scores_added[0])
        except Exception as e:
            raise f'赋值时发生错误{e}'

    @staticmethod
    def get_level(score, min_score):
        """
         计算赋分学科的等级
        :param score: 学科的卷面得分
        :param min_score: 所在等级的最低分
        :return: 相应的等级
        """
        if score >= min_score[0]:
            return 'A'
        elif score >= min_score[1]:
            return 'B'
        elif score >= min_score[2]:
            return 'C'
        elif score >= min_score[3]:
            return 'D'
        elif score < min_score[3]:
            return 'E'

    @staticmethod
    def change_columns_order(data):
        col_first = '参考人数'
        cols_left = [col for col in data.columns if col != col_first]
        new_columns_data = data[[col_first] + cols_left]
        return new_columns_data

    @staticmethod
    def get_subject_good_score(data, subject, line_ratio):
        """
         计算学科有效分。按照总分上线率，计算学科有效分
        :param data: DataFrame
        :param subject: 学科
        :param line_ratio: 总分（分为高线和中线）
        :return: 学科的有效分（高线和低线）
        """
        # total_num = data.shape[0]
        # good_total_num = data.loc[data[total_col] >= GaokaoData2025.total_line].shape[0]
        # good_percent_ratio = good_total_num / total_num
        good_subject_num = int(data[subject].count() * line_ratio)
        data.sort_values(by=subject, ascending=False, inplace=True, ignore_index=True)
        good_subject_score = data.loc[:good_subject_num - 1, subject].min()

        return good_subject_score

    @staticmethod
    def get_good_total_line(data, total_col, line_ratio):
        """
        搂指定的比例计算划线线分数
        :param data:
        :param total_col:
        :param line_ratio
        :return:
        """
        data.sort_values(by=total_col, ascending=False, inplace=True, ignore_index=True)
        # total_num = data.shape[0]
        good_total_num = int(data[total_col].count() * line_ratio)
        good_total_line = data.loc[:good_total_num - 1, total_col].min()
        return good_total_line

    @staticmethod
    def filename_by_time():
        file_time = time.time()
        file_name = f'{file_time}.xlsx'
        return file_name

    def filename_by_category(self, **kwargs):
        category_ratio_percentage = [format(i, '.0%') for i in list(kwargs.values())]
        # file_time = time.time()
        if len(category_ratio_percentage) == 1:
            file_name = f'全科类-{category_ratio_percentage[0]}.xlsx'
            return file_name
        else:
            file_name = f'历史类-{category_ratio_percentage[0]}+物理类{category_ratio_percentage[1]}.xlsx'
            return file_name

    @staticmethod
    def split_into_class(data):
        class_names = data['班级'].unique()
        whole_class_df = pd.DataFrame()
        for class_name in class_names:
            class_name_df = data[data['班级'] == class_name].reset_index(drop=True)
            class_name_df['序号'] = [k + 1 for k in class_name_df.index]
            whole_class_df = pd.concat([whole_class_df, class_name_df])
        return whole_class_df

    @staticmethod
    def rename_columns(col_name, word):
        if word in col_name:
            return col_name.replace(word, '')
        else:
            return col_name

    @staticmethod
    def time_use(fn):
        def inner(*args, **kwargs):
            print(f'开始运行主函数{fn.__name__}'.center(106, '_'))
            start_time = time.time()
            result = fn(*args, **kwargs)
            end_time = time.time()
            time_used = end_time - start_time
            print(f'主函数{fn.__name__}结束运行'.center(106, '_'))
            print(f'运行主函数{fn.__name__}共计耗时{round(time_used, 2)}秒')
            return result

        return inner


class GaokaoDataProcess(GaokaoData2025):
    """
    用于区级及以上考试，不对学科赋分。用已知的有效分计算学科平均分，有效分，学科等级评估，错位人数及名单
    """
    subjects_good_scores_physics = {'语文': None, '数学': None, '英语': None, '物理': None, '化学': None,
                                    '生物': None, '政治': None, '地理': None, '总分': None}
    subjects_good_scores_history = {'语文': None, '数学': None, '英语': None, '历史': None, '生物': None,
                                    '政治': None, '地理': None, '总分': None}

    def __init__(self, file):
        super().__init__(file)
        self.file = file
        self.history_df = pd.read_excel(self.file, sheet_name='历史类')
        self.physics_df = pd.read_excel(self.file, sheet_name='物理类')

    def get_average(self):
        """
        计算区级及以上考试的各学科平均分。不能用赋分后的df来计算
        :return: 返回一个有df(物理类，历史类)元素的列表
        """
        data_list = [self.history_df, self.physics_df]
        final_av = []
        for data in data_list:
            # subjects = [col for col in data.columns if
            #             col in ['语文', '数学', '英语', '物理', '历史', '政治', '地理', '生物', '化学', '总分']]
            subjects = self.get_subjects(data)
            final_av_percentage = self.subjects_average(data, subjects)
            final_av.append(final_av_percentage)
            # print(final_av)

        return final_av

    def get_single_double_unmatched_data(self, data, **kwargs):
        """
         按照已知有效分，计算各学科有效分上线数据。主要用于区级及以上考试的成绩数据分析
        :param data:
        :param kwargs: 以字典形式输入各学科的有效分数
        :return: 单有效和双有效统计数据
        """
        subjects = self.get_subjects(data)
        single_data_list = []
        double_data_list = []
        double_subjects_num_list = []
        unmatched_subjects_num_list = []
        unmatched_students_list = []

        # total = kwargs['总分']
        for subject in subjects:
            # 计算某一科的总分上线人数
            double_subject_num = self.get_double_subject_num_series(data, subject, '总分', kwargs['总分'])
            # 计算某一学科的单有效，双有效，错位生名单
            single_data = self.get_single_subject_data(data, subject, kwargs[subject])
            double_data = self.get_double_subject_data(data, subject, '总分', kwargs[subject], kwargs['总分'])
            double_subjects_num_list.append(double_subject_num)
            single_data_list.append(single_data)
            double_data_list.append(double_data)
            subject_unmatched_student = self.get_unmatched_subject_students(data, '总分', subject,
                                                                            kwargs[subject], kwargs['总分'])
            unmatched_subject_num = subject_unmatched_student.groupby('班级')[subject].count()
            unmatched_subjects_num_list.append(unmatched_subject_num)
            unmatched_students_list.append(subject_unmatched_student)
        # 计算错位人数
        unmatched_subjects_num = pd.concat(unmatched_subjects_num_list, axis=1)
        unmatched_subjects_num.loc['年级共计'] = [
            unmatched_subjects_num[col].sum() for col in
            unmatched_subjects_num.columns]
        # 计算选科后有效总数
        double_subjects_num_df = pd.concat(double_subjects_num_list, axis=1)

        double_subjects_num_df.loc['年级共计'] = [
            double_subjects_num_df[col].sum() for col in
            double_subjects_num_df.columns]
        # print(double_subjects_num_df)
        # 计算学科错位学生名单
        unmatched_students = pd.concat(unmatched_students_list, axis=1)
        unmatched_students.dropna(axis=1, how='all', inplace=True)
        single_data = pd.concat(single_data_list, axis=1)
        double_data = pd.concat(double_data_list, axis=1)
        # 单有效统计：增加一列参考人数和一行年级共计
        single_data['参考人数'] = data['班级'].value_counts()
        single_data.loc['年级共计'] = [single_data[col].sum() for col in single_data.columns]
        # 增加一列上线率并用百分号显示
        single_data['上线率'] = single_data['总分'] / single_data['参考人数']
        single_data['上线率'] = single_data['上线率'].apply(lambda x: format(x, '.2%'))  # 以百分号显示
        # 双有效统计：增加一列参考人数和一行年级共计
        double_data['参考人数'] = data['班级'].value_counts()
        double_data.loc['年级共计'] = [double_data[col].sum() for col in double_data.columns]
        # 计算学科评价，获取命中率，贡献率和评价等级
        assessment_df, shoot_df, contribution_df = self.get_assess_grade_df(single_data, double_data,
                                                                            double_subjects_num_df)
        subjects_assessment = [col for col in data.columns if
                               col in ['语文', '数学', '英语', '物理', '历史', '化学', '生物', '政治', '地理']]
        # 给各班各学科评价赋值：A，B，C，D，E，F（或无数据）
        for subject in subjects_assessment:
            self.grade_assessment(subject, shoot_df, contribution_df, single_data, assessment_df,
                                  double_subjects_num_df)
        # print(assessment_df)
        # 注意：新版用map，旧版用applymap
        shoot_df = shoot_df.map(lambda x: format(x, '.2%'))
        contribution_df = contribution_df.map(lambda x: format(x, '.2%'))
        combined_assessment = pd.concat([shoot_df, contribution_df, assessment_df], axis=0,
                                        keys=['命中率', '贡献率', '学科评价'])

        return single_data, double_data, unmatched_subjects_num, unmatched_students, assessment_df, combined_assessment

    def good_scores(self):
        """
         用区级及以上考试的有效分统计，用所给的有效分数计算各班各科有效分上线情况
        """
        single_double_unmatched_list = []
        unmatched_students_list = []
        assessment_list = []
        combined_assessment_list = []

        good_subjects_score_physics = pd.DataFrame(GaokaoDataProcess.subjects_good_scores_physics, index=[0])
        good_subjects_score_history = pd.DataFrame(GaokaoDataProcess.subjects_good_scores_history, index=[0])
        # 物理类数据处理
        # data_physics = [data for data in self.data_list if '物理' in data.columns][0]
        data_physics = self.physics_df
        (single_physics, double_physics, unmatched_num_physics, unmatched_students_physics,
         assessment_df_physics, combined_assessment_physics) = (
            self.get_single_double_unmatched_data(
                data_physics, **GaokaoDataProcess.subjects_good_scores_physics))
        # 合成有学科效分数，单有效，双有效和错位人数df
        single_double_unmatched_physics = pd.concat(
            [good_subjects_score_physics, single_physics, double_physics, unmatched_num_physics, assessment_df_physics],
            axis=0,
            keys=['学科有效分数', '单有效', '双有效', '错位人数', '学科评价'])
        # 把参考人数列改到第一列
        single_double_unmatched_physics = self.change_columns_order(single_double_unmatched_physics)
        single_double_unmatched_list.append(single_double_unmatched_physics)
        # 计算错位生名单和学科评价，放入列表
        unmatched_students_list.append(unmatched_students_physics)
        assessment_list.append(assessment_df_physics)
        combined_assessment_list.append(combined_assessment_physics)
        # 历史类数据处理
        # data_history = [data for data in self.data_list if '历史' in data.columns][0]
        data_history = self.history_df
        (single_history, double_history, unmatched_num_history, unmatched_students_history,
         assessment_df_history, combined_assessment_history) = (
            self.get_single_double_unmatched_data(
                data_history, **GaokaoDataProcess.subjects_good_scores_history))
        # 合成有学科效分数，单有效，双有效和错位人数df
        single_double_unmatched_history = pd.concat(
            [good_subjects_score_history, single_history, double_history, unmatched_num_history, assessment_df_history],
            axis=0,
            keys=['学科有效分数', '单有效', '双有效', '错位人数', '学科评价'])
        single_double_unmatched_history = self.change_columns_order(single_double_unmatched_history)
        # 计算错位生名单和学科评价，放入列表
        single_double_unmatched_list.append(single_double_unmatched_history)
        unmatched_students_list.append(unmatched_students_history)
        assessment_list.append(assessment_df_history)
        combined_assessment_list.append(combined_assessment_history)
        return single_double_unmatched_list, unmatched_students_list, assessment_list, combined_assessment_list

    def excel_files(self):
        """
         区级及以上考试不用赋分,excel表上有物理类和历史类两个工作表（sheet)
        """
        average_added = self.get_average()
        good_scores_list, unmatched_students_names_list, assessment_list, combined_assessment_list = self.good_scores()

        excel_file_name, output_path = self.get_excel_name()
        with pd.ExcelWriter(output_path) as writer:
            for item in range(len(average_added)):
                average_added[item].to_excel(writer, sheet_name=f'{self.sheet_names[item]}-平均分统计')
            if len(self.sheet_names) == 1:
                good_scores_list[0].to_excel(writer, sheet_name=f'{self.sheet_names[item]}--有效分')
            else:
                good_scores_list[0].to_excel(writer, sheet_name='物理类有效分统计')
                good_scores_list[1].to_excel(writer, sheet_name='历史类有效分统计')
                unmatched_students_names_list[0].to_excel(writer, sheet_name='物理类错位生名单', index=False)
                unmatched_students_names_list[1].to_excel(writer, sheet_name='历史类错位生名单', index=False)
                combined_assessment_list[0].to_excel(writer, sheet_name='物理类学科评价')
                combined_assessment_list[1].to_excel(writer, sheet_name='历史类学科评价')
        return excel_file_name, good_scores_list[0], good_scores_list[1]


class JuniorExam(GaokaoData2025):

    def __init__(self, filepath):
        super().__init__(filepath)
        self.filepath = filepath
        self.df = pd.read_excel(self.filepath, sheet_name='总表')

    def junior_scores(self):
        """
        计算初中考试合格人数及合格率
        :return: 生成一个excel文件
        """
        if '化学' in self.df.columns:
            qualification_df = self.df.loc[:,
                               ['序号', '姓名', '班级', '语文A卷', '数学A卷', '英语A卷', '物理A卷', '化学', '总分']]
            # qualification_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '化学', '总分']

            # 计算合格人数
            single_chn = qualification_df[qualification_df['语文A卷'] >= 60].groupby(['班级'])['语文A卷'].count()
            single_math = qualification_df[qualification_df['数学A卷'] >= 60].groupby(['班级'])['数学A卷'].count()
            single_eng = qualification_df[qualification_df['英语A卷'] >= 60].groupby(['班级'])['英语A卷'].count()
            single_phys = qualification_df[qualification_df['物理A卷'] >= 60].groupby(['班级'])['物理A卷'].count()
            single_chem = qualification_df[qualification_df['化学'] >= 60].groupby(['班级'])['化学'].count()

            name_num = qualification_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            qualified_data = pd.concat([name_num, single_chn, single_math, single_eng,
                                        single_phys, single_chem],
                                       axis=1)

            qualified_data.loc['年级'] = [qualified_data['参考人数'].sum(),
                                          qualified_data['语文A卷'].sum(),
                                          qualified_data['数学A卷'].sum(),
                                          qualified_data['英语A卷'].sum(),
                                          qualified_data['物理A卷'].sum(),
                                          qualified_data['化学'].sum()]
            # 计算合格率
            qualified_data['语文合格率'] = qualified_data['语文A卷'] / qualified_data['参考人数']
            qualified_data['语文合格率'] = qualified_data['语文合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['数学合格率'] = qualified_data['数学A卷'] / qualified_data['参考人数']
            qualified_data['数学合格率'] = qualified_data['数学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['英语合格率'] = qualified_data['英语A卷'] / qualified_data['参考人数']
            qualified_data['英语合格率'] = qualified_data['英语合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['物理合格率'] = qualified_data['物理A卷'] / qualified_data['参考人数']
            qualified_data['物理合格率'] = qualified_data['物理合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['化学合格率'] = qualified_data['化学'] / qualified_data['参考人数']
            qualified_data['化学合格率'] = qualified_data['化学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data.reset_index(inplace=True)
            new_columns = ['班级', '参考人数', '语文A卷', '语文合格率', '数学A卷', '数学合格率', '英语A卷',
                           '英语合格率',
                           '物理A卷', '物理合格率', '化学', '化学合格率']
            qualified_data = qualified_data[new_columns]

            return qualified_data
        elif '物理A卷' not in self.df.columns:
            qualification_df = self.df.loc[:, ['序号', '姓名', '班级', '语文A卷', '数学A卷', '英语A卷', '总分']]
            # qualification_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '总分']

            # 计算合格人数
            single_chn = qualification_df[qualification_df['语文A卷'] >= 60].groupby(['班级'])['语文A卷'].count()
            single_math = qualification_df[qualification_df['数学A卷'] >= 60].groupby(['班级'])['数学A卷'].count()
            single_eng = qualification_df[qualification_df['英语A卷'] >= 60].groupby(['班级'])['英语A卷'].count()

            name_num = qualification_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            qualified_data = pd.concat([name_num, single_chn, single_math, single_eng], axis=1)

            qualified_data.loc['年级'] = [qualified_data['参考人数'].sum(),
                                          qualified_data['语文A卷'].sum(),
                                          qualified_data['数学A卷'].sum(),
                                          qualified_data['英语A卷'].sum(),
                                          ]
            # 计算合格率
            qualified_data['语文合格率'] = qualified_data['语文A卷'] / qualified_data['参考人数']
            qualified_data['语文合格率'] = qualified_data['语文合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['数学合格率'] = qualified_data['数学A卷'] / qualified_data['参考人数']
            qualified_data['数学合格率'] = qualified_data['数学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['英语合格率'] = qualified_data['英语A卷'] / qualified_data['参考人数']
            qualified_data['英语合格率'] = qualified_data['英语合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data.reset_index(inplace=True)
            new_columns = ['班级', '参考人数', '语文A卷', '语文合格率', '数学A卷', '数学合格率', '英语A卷',
                           '英语合格率']
            qualified_data = qualified_data[new_columns]

            return qualified_data
        else:
            qualification_df = self.df.loc[:,
                               ['序号', '姓名', '班级', '语文A卷', '数学A卷', '英语A卷', '物理A卷', '总分']]
            # qualification_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '总分']

            # 计算合格人数
            single_chn = qualification_df[qualification_df['语文A卷'] >= 60].groupby(['班级'])['语文A卷'].count()
            single_math = qualification_df[qualification_df['数学A卷'] >= 60].groupby(['班级'])['数学A卷'].count()
            single_eng = qualification_df[qualification_df['英语A卷'] >= 60].groupby(['班级'])['英语A卷'].count()
            single_phys = qualification_df[qualification_df['物理A卷'] >= 60].groupby(['班级'])['物理A卷'].count()

            name_num = qualification_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            qualified_data = pd.concat([name_num, single_chn, single_math, single_eng,
                                        single_phys], axis=1)

            qualified_data.loc['年级'] = [qualified_data['参考人数'].sum(),
                                          qualified_data['语文A卷'].sum(),
                                          qualified_data['数学A卷'].sum(),
                                          qualified_data['英语A卷'].sum(),
                                          qualified_data['物理A卷'].sum()]
            # 计算合格率
            qualified_data['语文合格率'] = qualified_data['语文A卷'] / qualified_data['参考人数']
            qualified_data['语文合格率'] = qualified_data['语文合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['数学合格率'] = qualified_data['数学A卷'] / qualified_data['参考人数']
            qualified_data['数学合格率'] = qualified_data['数学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['英语合格率'] = qualified_data['英语A卷'] / qualified_data['参考人数']
            qualified_data['英语合格率'] = qualified_data['英语合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['物理合格率'] = qualified_data['物理A卷'] / qualified_data['参考人数']
            qualified_data['物理合格率'] = qualified_data['物理合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data.reset_index(inplace=True)
            new_columns = ['班级', '参考人数', '语文A卷', '语文合格率', '数学A卷', '数学合格率', '英语A卷',
                           '英语合格率',
                           '物理A卷', '物理合格率']
            qualified_data = qualified_data[new_columns]

            return qualified_data

    def get_av(self):
        # 计算平均分
        if '化学' in self.df.columns:

            av_class = self.df.groupby(['班级'])[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                                  '英语A卷', '英语B卷', '英语合卷', '物理A卷', '物理B卷', '物理合卷',
                                                  '化学', '总分']].mean().round(2)
            av_general = self.df[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                  '英语A卷', '英语B卷', '英语合卷', '物理A卷', '物理B卷', '物理合卷',
                                  '化学', '总分']].apply(np.nanmean, axis=0).round(2)
            av_general.name = '年级平均分'
            av_scores = av_class.append(av_general)
            av_scores.reset_index(inplace=True)

            return av_scores
        elif '物理合卷' not in self.df.columns:
            av_class = self.df.groupby(['班级'])[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                                  '英语A卷', '英语B卷', '英语合卷', '总分']].mean().round(2)
            av_general = self.df[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                  '英语A卷', '英语B卷', '英语合卷', '总分']].apply(np.nanmean, axis=0).round(2)
            av_general.name = '年级平均分'
            av_scores = av_class.append(av_general)
            av_scores.reset_index(inplace=True)
            return av_scores
        else:
            av_class = self.df.groupby(['班级'])[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                                  '英语A卷', '英语B卷', '英语合卷',
                                                  '物理A卷', '物理B卷', '物理合卷', '总分']].mean().round(2)
            av_general = self.df[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                  '英语A卷', '英语B卷', '英语合卷', '物理A卷',
                                  '物理B卷', '物理合卷', '总分']].apply(np.nanmean, axis=0).round(2)
            av_general.name = '年级平均分'
            av_scores = av_class.append(av_general)
            av_scores.reset_index(inplace=True)
            return av_scores

    def get_goodscores(self, goodtotal):
        """
        计算各科有效分
        goodtotal:划线总分，高线，中线，低线
        """
        if '化学' in self.df.columns:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '化学', '总分']]
            # good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '化学', '总分']

            goodscoredata = good_score_df.loc[good_score_df['总分'] >= goodtotal]
            chnav = goodscoredata['语文合卷'].mean()
            mathav = goodscoredata['数学合卷'].mean()
            engav = goodscoredata['英语合卷'].mean()
            phyav = goodscoredata['物理合卷'].mean()
            chemav = goodscoredata['化学'].mean()
            totalav = goodscoredata['总分'].mean()
            factor = goodtotal / totalav
            chn = round(chnav * factor)
            math = round(mathav * factor)
            eng = round(engav * factor)
            phy = round(phyav * factor)
            chem = round(chemav * factor)
            if (chn + math + eng + phy + chem) > goodtotal:
                math -= 1
            if (chn + math + eng + phy + chem) < goodtotal:
                chn += 1
            return chn, math, eng, phy, chem, goodtotal

        elif '物理合卷' not in self.df.columns:
            good_score_df = self.df.loc[:, ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '总分']]
            # good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '总分']

            goodscoredata = good_score_df.loc[good_score_df['总分'] >= goodtotal]
            chnav = goodscoredata['语文合卷'].mean()
            mathav = goodscoredata['数学合卷'].mean()
            engav = goodscoredata['英语合卷'].mean()
            totalav = goodscoredata['总分'].mean()

            factor = goodtotal / totalav

            chn = round(chnav * factor)
            math = round(mathav * factor)
            eng = round(engav * factor)

            if (chn + math + eng) > goodtotal:
                math -= 1
            if (chn + math + eng) < goodtotal:
                chn += 1
            return chn, math, eng, goodtotal

        else:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '总分']]
            # good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '总分']
            goodscoredata = good_score_df.loc[good_score_df['总分'] >= goodtotal]
            chnav = goodscoredata['语文合卷'].mean()
            mathav = goodscoredata['数学合卷'].mean()
            engav = goodscoredata['英语合卷'].mean()
            phyav = goodscoredata['物理合卷'].mean()
            totalav = goodscoredata['总分'].mean()
            factor = goodtotal / totalav
            chn = round(chnav * factor)
            math = round(mathav * factor)
            eng = round(engav * factor)
            phy = round(phyav * factor)
            if (chn + math + eng + phy) > goodtotal:
                math -= 1
            if (chn + math + eng + phy) < goodtotal:
                chn += 1
            return chn, math, eng, phy, goodtotal

    def goodscore_process(self, goodtotal):
        """
        计算各科各班单有效和双有效人数
        """
        # 计算各班有效学生人数

        if '化学' in self.df.columns:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '化学', '总分']]
            good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '化学', '总分']

            chn, math, eng, phy, chem, total = self.get_goodscores(goodtotal)
            single_chn, double_chn = self.get_single_double_score(good_score_df, '语文', chn, total)
            single_math, double_math = self.get_single_double_score(good_score_df, '数学', math, total)
            single_eng, double_eng = self.get_single_double_score(good_score_df, '英语', eng, total)
            single_phy, double_phy = self.get_single_double_score(good_score_df, '物理', phy, total)
            single_chem, double_chem = self.get_single_double_score(good_score_df, '化学', chem, total)
            single_total, double_total = self.get_single_double_score(good_score_df, '总分', total, total)

            # 计算参考人数
            name_num = good_score_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng,
                              '物理': phy, '化学': chem, '总分': total}
            goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

            result_single = pd.concat([name_num, single_chn, single_math, single_eng,
                                       single_phy, single_chem, single_total],
                                      axis=1)

            result_double = pd.concat(
                [name_num, double_chn, double_math, double_eng,
                 double_phy, double_chem, double_total], axis=1)

            result_single.loc['共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['物理'].sum(),
                                         result_single['化学'].sum(),
                                         result_single['总分'].sum()
                                         ]
            # 新增上线率一列并用百分数表示
            result_single['上线率'] = result_single['总分'] / result_single['参考人数']
            result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
            # 新增一行文科共计。
            result_double.loc['共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['物理'].sum(),
                                         result_double['化学'].sum(),
                                         result_double['总分'].sum()
                                         ]
            final_result = pd.concat([goodscore_df, result_single, result_double], keys=['有效分', '单有效', '双有效'])

            return final_result
        elif '物理合卷' not in self.df.columns:
            good_score_df = self.df.loc[:, ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '总分']]
            good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '总分']

            chn, math, eng, total = self.get_goodscores(goodtotal)
            single_chn, double_chn = self.get_single_double_score(good_score_df, '语文', chn, total)
            single_math, double_math = self.get_single_double_score(good_score_df, '数学', math, total)
            single_eng, double_eng = self.get_single_double_score(good_score_df, '英语', eng, total)
            single_total, double_total = self.get_single_double_score(good_score_df, '总分', total, total)

            # 计算参考人数
            name_num = good_score_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng, '总分': total}
            goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

            result_single = pd.concat([name_num, single_chn, single_math, single_eng, single_total], axis=1)

            result_double = pd.concat(
                [name_num, double_chn, double_math, double_eng, double_total], axis=1)

            result_single.loc['共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['总分'].sum()]
            # 新增上线率一列并用百分数表示
            result_single['上线率'] = result_single['总分'] / result_single['参考人数']
            result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
            # 新增一行文科共计。
            result_double.loc['共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['总分'].sum()]
            final_result = pd.concat([goodscore_df, result_single, result_double], keys=['有效分', '单有效', '双有效'])

            return final_result

        else:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '总分']]
            good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '总分']
            chn, math, eng, phy, total = self.get_goodscores(goodtotal)
            single_chn, double_chn = self.get_single_double_score(good_score_df, '语文', chn, total)
            single_math, double_math = self.get_single_double_score(good_score_df, '数学', math, total)
            single_eng, double_eng = self.get_single_double_score(good_score_df, '英语', eng, total)
            single_phy, double_phy = self.get_single_double_score(good_score_df, '物理', phy, total)
            single_total, double_total = self.get_single_double_score(good_score_df, '总分', total, total)

            # 计算参考人数
            name_num = good_score_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng,
                              '物理': phy, '总分': total}
            goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

            result_single = pd.concat([name_num, single_chn, single_math, single_eng,
                                       single_phy, single_total],
                                      axis=1)

            result_double = pd.concat(
                [name_num, double_chn, double_math, double_eng,
                 double_phy, double_total], axis=1)

            result_single.loc['共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['物理'].sum(),
                                         result_single['总分'].sum()
                                         ]
            # 新增上线率一列并用百分数表示
            result_single['上线率'] = result_single['总分'] / result_single['参考人数']
            result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
            # 新增一行文科共计。
            result_double.loc['共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['物理'].sum(),
                                         result_double['总分'].sum()
                                         ]
            final_result = pd.concat([goodscore_df, result_single, result_double], keys=['有效分', '单有效', '双有效'])

            return final_result

    def concat_results(self, goodtatal=None):
        good_score_results = self.goodscore_process(goodtatal)
        junior_av = self.get_av()
        junior_qualification = self.junior_scores()
        good_score_results = self.write_open(good_score_results)
        junior_av = self.write_open(junior_av)
        junior_qualification = self.write_open(junior_qualification)
        final_results = pd.concat([good_score_results, junior_av, junior_qualification])
        filename, output_dir = self.get_excel_name()
        with pd.ExcelWriter(output_dir) as writer:
            final_results.to_excel(writer, sheet_name='成绩统计', index=False)
        return filename

    @staticmethod
    def get_single_double_score(data, subject, subject_score, total_score):
        single = data[data[subject] >= subject_score].groupby(['班级'])[subject].count()
        data_double = data[data['总分'] >= total_score]
        double = data_double[data_double[subject] >= subject_score].groupby(['班级'])[subject].count()
        return single, double

    @staticmethod
    def write_open(df_data):
        df_data.to_excel('temp_data.xlsx')
        df_new = pd.read_excel('temp_data.xlsx', header=None)
        os.remove('temp_data.xlsx')
        return df_new


if __name__ == '__main__':
    file_path = r'D:\data_test_files\高2022级零诊成绩测试数据.xlsx'
    # 物理类各科有效分
    GaokaoDataProcess.subjects_good_scores_physics = {'语文': 84, '数学': 64, '英语': 63, '物理': 25, '政治': 61,
                                                      '地理': 61, '化学': 56, '生物': 57, '总分': 370}
    # 历史类各科有效分
    GaokaoDataProcess.subjects_good_scores_history = {'语文': 87, '数学': 45, '英语': 62, '历史': 46, '政治': 62,
                                                      '地理': 63, '生物': 52, '总分': 370}


    @GaokaoDataProcess.time_use
    def main():
        exam = GaokaoDataProcess(file_path)
        exam.excel_files()


    # run the program
    main()
